package br.sed.theenemy.model.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import br.sed.theenemy.business.exception.DBException;
import br.sed.theenemy.business.exception.InvalidUniqueBeanException;
import br.sed.theenemy.business.exception.InvalidUserException;
import br.sed.theenemy.model.bean.Action;
import br.sed.theenemy.presentation.EnemyConstants;
import br.sed.theenemy.presentation.vo.ActionVO;
import br.sed.theenemy.presentation.vo.UserVO;

/**
 * 
 * @author JulioCB
 * @email jcbjulio@gmail.com
 * 
 */
public class ActionDao extends AbstractDao{
	
	private static DateFormat DATE_FORMAT_DB = new SimpleDateFormat(EnemyConstants.SIMPLE_DATE_FORMAT_DB);
	
	// Unica instancia da classe
	protected static ActionDao instanceDao;
	
	public void create(Action action) throws DBException{
		String sql = "insert into actions (the_id, id_type, id_user, id_action_rel, comment) values " +
						 "("+getId()+"," +
						 "'"+action.getTypeId()+"', " +
						 "'"+action.getUserId()+"', " +
						 "'"+action.getActionIdRel()+"', " +
						 "'"+action.getComment()+"')";			

		executeQuery(sql, "Error: Insert Action");
	}
	
	public Action findById(Long id) throws SQLException, ParseException, NumberFormatException, InvalidUniqueBeanException, InvalidUserException, DBException{
		String sql = "select * from actions a " +
						"where a.the_id = "+ id;
		ResultSet result = null;
		result = getRS(sql);
		
		Action action = getAction(result);
		result.close();
		closeConnection();
		return action;
	}
	
	public Action findLastCommentByUserId(Long userId) throws SQLException, ParseException, NumberFormatException, DBException{
		String sql = "select * from actions a " +
						"where a.id_user = "+ userId +" AND a.id_type = 1 order by date_time desc";
		ResultSet result = null;
		result = getRS(sql);
		
		List<Action> actionList = fillActionList(result);
		result.close();
		closeConnection();
		if(actionList.size()>=1){
			return actionList.get(0);
		}else{
			return null;
		}
	}
	
	public Action findUserCreation(Long userId) throws SQLException, NumberFormatException, ParseException, DBException{
	    String sql = "select * from actions a " +
        "where a.id_user = "+ userId +" AND a.id_type = 0 order by date_time desc";
        ResultSet result = null;
        result = getRS(sql);
        
        List<Action> actionList = fillActionList(result);
        result.close();
        closeConnection();
        if(actionList.size()>=1){
            return actionList.get(0);
        }else{
            //TODO throw ...
            return null;
        }
	}
	
	public List<Action> findAllByActionId(Long actionId) throws SQLException, NumberFormatException, ParseException, DBException{
		String sql = "select * from actions a " +
						"where a.id_action_rel = "+ actionId +" order by the_id asc";
		ResultSet result = null;
		result = getRS(sql);

		List<Action> actionList = fillActionList(result);
		result.close();
        closeConnection();
		return actionList;
	}
	
	public void updateUserSide(Long userId, Long actionIdRel, Long type) throws DBException{
	    String sql = "update actions set id_type = "+ type +" where id_action_rel = "+ actionIdRel + " and  id_user = "+ userId
	                    +" and ( id_type = "+ EnemyConstants.SUPPORT +" or id_type = "+ EnemyConstants.DEFEND +" )";
        
        executeQuery(sql, "Error: Insert Action");
	}
	
	public ActionVO findActionStreamById(Long actionId) throws SQLException, ParseException, NumberFormatException, InvalidUniqueBeanException, InvalidUserException, DBException{
	    String sql = "select * from MainStream m " +
        "where m.actionId = "+ actionId;
        ResultSet result = null;
        result = getRS(sql);        
        ActionVO actionVO = getActionStream(result);
        result.close();
        closeConnection();
        return actionVO;
    }
	
	public List<ActionVO> findActionStream(Long userId) throws SQLException, DBException{
	    String sql = "select * from MainStream m " +
        "where m.defenderId = "+ userId +" or m.atackerId = "+ userId +" order by m.actionId desc";
	    ResultSet result = null;
	    result = getRS(sql);	    
	    List<ActionVO> actionListVO = fillActionStreamList(result);
	    result.close();
        closeConnection();
	    return actionListVO;
	}
	
	public List<ActionVO> findAllActionStream(Long userId) throws SQLException, DBException{
        String sql = "select * from MainStream MS where MS.atackerId in (select id_target from contacts where id_source ="+ 
                        userId +") or MS.defenderId in (select id_target from contacts where id_source = "+ userId+
                        ") or atackerId = "+ userId +" or defenderId = "+ userId +" order by MS.actionId desc limit 20";
        ResultSet result = null;
        result = getRS(sql);        
        List<ActionVO> actionListVO = fillActionStreamList(result);
        result.close();
        closeConnection();
        return actionListVO;
    }
	
	//SELECT act.the_id as ActionId, Count(*) as Comment FROM actions act inner join actions sub on act.the_id = sub.id_action_rel where act.id_type = 2 AND sub.id_type = 4 group by act.the_id
	
	private List<ActionVO> fillActionStreamList(ResultSet result) throws NumberFormatException, SQLException{
	    List<ActionVO> actionListVO = new ArrayList<ActionVO>();
        while(result.next()){
            ActionVO actionVO = new ActionVO();
            actionVO.setId(Long.parseLong(result.getString("actionId")));
            actionVO.setActionType(Long.parseLong(result.getString("type")));
            actionVO.setActionRelId(Long.parseLong(result.getString("actionRelated")));
            
            UserVO attackerVO = new UserVO();
            attackerVO.setId(Long.parseLong(result.getString("atackerId")));
            attackerVO.setFirstName(result.getString("attackerFirstName"));
            attackerVO.setLastName(result.getString("attackerLastName"));
            attackerVO.setImage(result.getString("attackerPhoto"));
            actionVO.setUserAttack(attackerVO);
            if(actionVO.getActionType()==EnemyConstants.ATTACK){
                UserVO defenderVO = new UserVO();
                defenderVO.setId(Long.parseLong(result.getString("defenderId")));
                defenderVO.setFirstName(result.getString("defenderFirstName"));
                defenderVO.setLastName(result.getString("defenderLastName"));
                defenderVO.setImage(result.getString("defenderPhoto"));
                actionVO.setUserDefend(defenderVO);
            }
            
            actionVO.setComment(result.getString("comment"));
            actionListVO.add(actionVO);
        }
        return actionListVO;
	}
	
	private ActionVO getActionStream(ResultSet result) throws InvalidUniqueBeanException, NumberFormatException, SQLException, ParseException, InvalidUserException{
        List<ActionVO> actionListVO = fillActionStreamList(result);
        
        if(actionListVO.size()==1){
            return actionListVO.get(0);   
        }else if(actionListVO.size()==0){
            throw new InvalidUserException("Invalid User Exception");
        }else{
            throw new InvalidUniqueBeanException("Error getting Action, not unique");
        }
    }
	
	private List<Action> fillActionList(ResultSet result) throws NumberFormatException, SQLException, ParseException{
		List<Action> actionList = new ArrayList<Action>();
		while(result.next()){
			Action action = new Action();
			action.setId(Long.parseLong(result.getString("the_id")));
			action.setTypeId(Long.parseLong(result.getString("id_type")));
			action.setUserId(Long.parseLong(result.getString("id_user")));
			action.setActionIdRel(Long.parseLong(result.getString("id_action_rel")));
			action.setCreationDate(DATE_FORMAT_DB.parse(result.getString("date_time")));
			action.setComment(result.getString("comment"));
			actionList.add(action);
		}
		return actionList;
	}
	
	private Action getAction(ResultSet result) throws InvalidUniqueBeanException, NumberFormatException, SQLException, ParseException, InvalidUserException{
		List<Action> actionList = fillActionList(result);
		if(actionList.size()==1){
			return actionList.get(0);	
		}else if(actionList.size()==0){
			throw new InvalidUserException("Invalid User Exception");
		}else{
			throw new InvalidUniqueBeanException("Error getting Action, not unique");
		}
	}
	
	public static ActionDao getInstance(){
		
		// Verifica se ja tem instancia
		if ( instanceDao == null ){			
			instanceDao = new ActionDao();			
		}
		
		return instanceDao;
		
	}
}
